package com.wolfpire.chapter.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.chapter.dao.ChapterDao;
import com.wolfpire.knowledge.dao.KnowledgeDao;
import com.wolfpire.subject.dao.SubjectDao;
import com.wolfpire.system.common.Constants;
import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.system.dao.UserDao;
import com.wolfpire.system.model.Chapter;
import com.wolfpire.system.model.DataDict;
import com.wolfpire.system.model.Knowledge;
import com.wolfpire.system.model.Profession;
import com.wolfpire.system.model.Subject;
import com.wolfpire.system.model.User;

@Repository("chapterDao")
public class ChapterDaoImpl extends BaseHibernateDao<Chapter, Long> implements ChapterDao {
	
	public List<Chapter> list(Chapter chapter) {
		Criteria c = createCriteria(createFilter(chapter));
		return c.list();
	}

	public Page<Chapter> findChapters(Page<Chapter> page, Chapter filterChapter) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		//querySqlBuffer.append(" SELECT u.* FROM t_sm_user u, t_sm_org o, t_sm_user_org uo ")
		querySqlBuffer.append(" SELECT c.* FROM t_chapter c  ")
				.append(" LEFT JOIN t_subject_chapter sc ON c.id = sc.chapter_id  ")
				.append(" LEFT JOIN t_subject_chapter s ON s.id = sc.subject_id ")
				.append(" LEFT JOIN t_profession_subject ps ON ps.subject_id = sc.subject_id ")
				.append(" WHERE 1 =1 ");
				//.append(" WHERE u.id = uo.user_id AND uo.org_id = o.id ");
		if (null != filterChapter.getId()) {
			querySqlBuffer.append(" c.id =:id");
			propertyNames.add("id");
			propertyValues.add(filterChapter.getId());
		}
		
		if (!StringUtils.isBlank(StringUtils.stripToEmpty(filterChapter.getName()))) {
			querySqlBuffer.append(" AND c.name LIKE :name ");
			propertyNames.add("name");
			propertyValues.add("%" + StringUtils.stripToEmpty(filterChapter.getName()) + "%");
		}
		
		if (null != filterChapter.getSubjectId()) {
			querySqlBuffer.append(" AND sc.subject_id= :subjectId ");
			propertyNames.add("subjectId");
			propertyValues.add(filterChapter.getSubjectId());
		}
		
		if (null != filterChapter.getProfessionId()) {
			querySqlBuffer.append(" AND ps.profession_id =:professionId ");
			propertyNames.add("professionId");
			propertyValues.add(filterChapter.getProfessionId());
		}
		
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS TOTAL ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY s.id DESC ");
		if (0 < totalCount) {
			List<Chapter> result = this.setPageParameter(page, Chapter.class, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(result);
		}
		
		return page;
	}

	public Chapter getByName(String name) {
		Criterion[] criterions = new Criterion[]{
				Restrictions.eq("name", name)
		};
		return findUniqueByCriteria(criterions);
	}
	
	private Criterion[] createFilter(Chapter chapter) {
		if (null == chapter) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		if (null != chapter.getId()) {
			criterions.add(Restrictions.eq("id", chapter.getId()));
		}
		if (!StringUtils.isBlank(chapter.getName())) {
			criterions.add(Restrictions.like("name", chapter.getName(), MatchMode.ANYWHERE));
		}
		
		return criterions.toArray(new Criterion[criterions.size()]);
	}

	public List<Map<String, Object>> queryChapterFullInfo() {
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT c.id, c.name, sc.subject_id FROM t_chapter c LEFT JOIN t_subject_chapter sc ON c.id = sc.chapter_id");
		SQLQuery query = this.createSqlQuery(querySql.toString(), null, null);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	public List<Chapter> getBySubjectId(long subjectId) {
		StringBuffer querySql = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySql.append(" select c.* from t_subject s,t_chapter c,t_subject_chapter sc ");
		querySql.append(" where s.id = sc.subject_id and c.id = sc.chapter_id and s.id = :subjectId ");
		propertyNames.add("subjectId");
		propertyValues.add(subjectId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames, propertyValues);
		query.addEntity(Chapter.class);
		return query.list();
	}
	
}
